/*
 * SystemClient_DBImpl.java
 *
 * Created on October 10, 2006, 8:27 PM
 */

package com.asaga.tools;

import com.sun.org.apache.xerces.internal.parsers.JAXPConfiguration;
import java.beans.*;
import java.io.Serializable;
import java.util.Date;
import java.util.ArrayList;
import java.sql.*;
import java.util.*;
import org.apache.taglibs.standard.tag.common.core.NullAttributeException;
import org.apache.tools.ant.taskdefs.Java;

/**
 * @author Jeremy Sanderlin
 */
public class SystemClient_DBImpl extends Object implements SystemClient {
    private String fname;
    private int id;
    private String state;
    private String city;
    private String addr1;
    private String addr2;
    private String lname;
    private String zip;
    private String phone;
    private String cell;
    private String email;
    private String password;
    private String fontColor;
    private String status;
    private String validation;
    private Date regDate;
    private Date lastVisit;
    private String welcomeMsg = "";
    private Event_DBImpl event;
    
    ArrayList userList;
    
    User tempUser;
    private int permissionId;
    private int groupId;
    private int eventId;
    private int classId;
    
    // Added by Jeremy
    //private JeremyDBConnectObj jdbcon;
    private String nickName;
    private DBPool connectionPool;
    private Connection conn;
    private String errmsg;
    private String dob;
    //private String classification;
    private ClassificationList classSet;
    Set classification;
    Set group; //may not use this
    Set groupMembershipSet;  //set of Group_DBImpl's
    
    //private ArrayList classification; // Classifications of user.
    
    public SystemClient_DBImpl() {
        connectionPool = DBPool.getInstance();
        setEvent(new Event_DBImpl());
        event.setCreatorId(id);
        errmsg = "";
    }
    /**
     * This method will query the database for all profile information for
     * the given user.  It will place the information into its private varibles,
     * e & p. Upon a successful retrieval of info, true will be returned otherwise
     * false is returned.
     * @param e Private variable
     * @param p Private variable
     * @return True upon successful retrieval of user information
     */
    public boolean getProfile(String e, String p){
        // Check to see if valid inputs
        if(e==null){
            return false;
        } else if(p==null){
            return false;
        }
        
        // Query db using username.
        //jdbcon = new JeremyDBConnectObj();
        //Connection conn = jdbcon.getConnection();
        
        
        conn = connectionPool.getConnection();
        ResultSet rs = null;
        
        String query = "select * from person where email='"+e+"'";
        
        try {
            java.sql.Statement statement = conn.createStatement();
            rs = statement.executeQuery(query);
            
            // Check to see if email is already in the database.
            if(rs.next()) // Check if it is an empty ResultSet. If so, return false.
            {
                if(rs.getString("email").equalsIgnoreCase(e)){
                    // Check to see if passwords match.
                    if(rs.getString("passwd").equals(p)){
                        this.id = rs.getInt("personID");
                        this.fname = rs.getString("fname");
                        this.lname = rs.getString("lname");
                        this.addr1 = rs.getString("addr1");
                        this.addr2 = rs.getString("addr2");
                        this.city = rs.getString("city");
                        this.state = rs.getString("state");
                        this.zip = rs.getString("zip");
                        this.phone = rs.getString("phone");
                        this.cell = rs.getString("cell");
                        this.email = rs.getString("email");
                        this.password = rs.getString("passwd");
                        this.fontColor= rs.getString("fontColor");
                        this.classification = new TreeSet();
                        this.groupMembershipSet = new TreeSet();
                     /*
                        ResultSet rsMyClassification = null;
                        String queryMyClassification =
                                "select * from person_has_classification where person_personID='"+this.id+"'";
                        try{
                            java.sql.Statement stMyClassification = conn.createStatement();
                            rsMyClassification = statement.executeQuery(queryMyClassification);
                            while(rsMyClassification.next()){
                                int myClassificationNumber = rsMyClassification.getInt("classification_classificationID");
                                this.classification.add(String.valueOf(myClassificationNumber));
                            }
                        }catch(SQLException ex){
                            
                        }
                        ResultSet rsMyGroupMembership = null;
                        String queryMyGroupMembership =
                                "select * from person_has_autismgroup where person_personID='"+this.id+"'";
                        try{
                            java.sql.Statement stMyGroupMembership = conn.createStatement();
                            rsMyGroupMembership = statement.executeQuery(queryMyGroupMembership);
                            while(rsMyGroupMembership.next()){
                                int myGroupMembershipNumber = rsMyGroupMembership.getInt("autismgroup_groupID");
                                Group_DBImpl myGroupMembership = new Group_DBImpl(myGroupMembershipNumber,conn);
                                this.groupMembershipSet.add(myGroupMembership);
                            }
                        }catch(SQLException ex){
                            
                        }*/
                        connectionPool.freeConnection(conn);
                        return true;
                    } else{
                        connectionPool.freeConnection(conn);
                        return false;
                    }
                }
            } else{
                connectionPool.freeConnection(conn);
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            this.errmsg = ex.toString();
            connectionPool.freeConnection(conn);
            return false;
        }
        connectionPool.freeConnection(conn);
        return false;
    }
    /**
     * This method will update the database with all private stored varibles
     * for the given person.  Returns true upon a successful update
     * otherwise returns false.
     * @param username The users username 
     * @return True upon successful update of profile
     */
    public boolean updateProfile(String username){
        //jdbcon = new JeremyDBConnectObj();
        conn = connectionPool.getConnection();
        ResultSet rs = null;
        // Check to see if valid inputs
        if(username==null||!username.equals(this.email)){
            connectionPool.freeConnection(conn);
            return false;
        } else{
            // Query db using username.
            String query = "select * from person where email='"+this.email+"'";
            try {
                java.sql.Statement statement = conn.createStatement();
                rs = statement.executeQuery(query);
                
                // Check to see if email is already in the database.
                if(rs.next()) {
                    if(rs.getString("email").equalsIgnoreCase(this.email)){
                        this.errmsg = "User profile found!";
                    }
                } else{
                    this.errmsg = "No valid user";
                    connectionPool.freeConnection(conn);
                    return false;
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
                this.errmsg = ex.toString();
                connectionPool.freeConnection(conn);
                return false;
            }
            
            // Authentication successful, update data from priv vars to db.
            String updateQuery01 = "UPDATE person SET";
            String updateQuery02 = "fname='"+this.fname+"',lname='"+this.lname+"" +
                    "',addr1='"+this.addr1+"',addr2='"+this.addr2+"',city='"+this.city+"" +
                    "',state='"+this.state+"',zip='"+this.zip+"',phone='"+this.phone+"" +
                    "',cell='"+this.cell+"',dob='"+this.dob+"',fontColor='"+this.fontColor+"'";
            //String updateQuery02 = "fname='Jeremy',lname='Sanderlin',addr1='54321',addr2='12345',city='athens',state='ga',zip='12345',phone='1234',cell='1234',dob='1234' ";
            String updateQuery03 = " WHERE email='"+this.email+"'";
            String updateQuery00 = updateQuery01+" "+updateQuery02+" "+updateQuery03;
            
            try {
                java.sql.Statement statement2 = conn.createStatement();
                int rowCount = statement2.executeUpdate(updateQuery00);
                this.errmsg = updateQuery00;
                
            } catch (SQLException ex) {
                ex.printStackTrace();
                this.errmsg = ex.toString();
                connectionPool.freeConnection(conn);
                return false;
            }
            
            //jdbcon.close();
            connectionPool.freeConnection(conn);
            return true;
        }
    }
    
    public boolean createProfile(){
        //jdbcon = new JeremyDBConnectObj();
        conn = connectionPool.getConnection();
        ResultSet rs = null;
        if(this.email==null||this.fname==null||this.lname==null||this.password==null){
            this.errmsg="Invalid data was submitted.  Please try to register again. <br/>"
                    + this.errmsg;
            //connectionPool.freeConnection(conn);
            return false;
        }
        String query = "select * from person where email='"+this.email+"'";
        try {
            java.sql.Statement statement = conn.createStatement();
            rs = statement.executeQuery(query);
            
            // Check to see if email is already in the database.
            if(rs.next()) {
                if(rs.getString("email").equalsIgnoreCase(this.email)){
                    this.errmsg = "This email address is already being used.";
                    connectionPool.freeConnection(conn);
                    return false;
                }
            } else{
                this.errmsg = "The email address is valid.";
            }
            
        } catch (SQLException ex) {
            ex.printStackTrace();
            this.errmsg = ex.toString();
            connectionPool.freeConnection(conn);
            return false;
        }
        
        // This is where the update statements will be placed.
        String insertQuery01 = "INSERT INTO person ";
        String insertQuery02 = "(fname,lname,addr1,addr2,city,state,zip,email,passwd,fontColor) ";
        String insertQuery03 = "VALUES ('"+this.fname+"','"+this.lname+"','"+this.addr1+
                "','"+this.addr2+"','"+this.city+"','"+this.state+"'," +
                "'"+this.zip+"','"+this.email+"','"+this.password+"','"+this.fontColor+"')";
        String insertQuery00 = insertQuery01+" "+insertQuery02+" "+insertQuery03;
        
        try {
            java.sql.Statement statement2 = conn.createStatement();
            int rowCount = statement2.executeUpdate(insertQuery00);
            
        } catch (SQLException ex) {
            ex.printStackTrace();
            this.errmsg = ex.toString();
            connectionPool.freeConnection(conn);
            return false;
        }
        
        //jdbcon.close();
        connectionPool.freeConnection(conn);
        return true; // for testing purposes only
    }
    
    // Getters and Setters Listed Below...
    
    public ClassificationList getClassSet(){return this.classSet;}
    public void setClassSet(ClassificationList c){this.classSet=c;}
    
    public String getNickName(){return this.nickName;}
    public void setNickName(String s){this.nickName=s;}
    
   /**
    * Method to get error messages
    * @return String errorMessage
    */
    public String getErrorMessage(){
        return this.errmsg;
    }
   /**
     * Method to set users id
     * @param i Sets ID
     */
    public void setId(int i){
        id = i;
    }
   /**
    * Method to get users id
    * @return int id
    */
    public int getId(){
        return id;
    }
   /**
     * Method to set users first name
     * @param f Sets fname
     */
    public void setFname(String f) {
        if(f.length()<1){
            fname = null;
            this.errmsg = this.errmsg +"\n <li> Please enter your first name. </li>";
        }else{
            fname = f;
        }
    }
   /**
    * Method to get users first name
    * @return String fname
    */
    public String getFname() {
        return fname;
    }
   /**
     * Method to set users last name
     * @param l l sets lname
     */
    public void setLname(String l) {
        if(l.length()<1){
            lname = null;
            this.errmsg = this.errmsg +"\n <li> Please enter your last name. </li>";
        }else{
            lname = l;
        }
    }
   /**
    * Method to get users last name
    * @return String lname
    */
    public String getLname() {
        return lname;      
    }
   /**
     * Method to set the first line of a users address. Generally street number
     * and name or name of business.
     * @param a1 a1 Sets addr1
     */
    public void setAddr1(String a1) {
        if(a1==null){
            addr1="null";
        } else{
            addr1 = a1;
        }
    }
    /**
    * Method to get the first line of a users address. Generally street name
    * and number
    * @return addr1
    */
    public String getAddr1() {
        return addr1;
    }
    /**
     * Method to set the second line of a users address. Generally apartment, condo or
     * suite number.
     * @param a2 a2 Sets addr2
     */ 
    public void setAddr2(String a2) {
        if(a2==null){
            addr2 = "null";
        } else{
            addr2 = a2;
        }
    }
   /**
    * Method to get the second line of a users address. Generally apartment, condo or
    * suite number.
    * @return addr2
    */ 
    public String getAddr2() {
        return addr2;
    }
    /**
     * Method to set the users city name. This is optional on form.
     * @param c c Sets String city
     */ 
    public void setCity(String c) {
        if(c==null){
            city = "null";
        } else {
            city = c;
        }
    }
   /**
    * Method to get the users city name.
    * @return city
    */  
    public String getCity() {
        return city;
    }
    /**
     * Method to set the users state name. This is optional on form.
     * @param s s Sets String state
     */
    public void setState(String s) {
        if(s==null){
            // do nothing
        } else {
            state = s;
        }
    }
    /**
    * Method to get the users state name.
    * @return String state
    */
    public String getState() {
        return state;
    }
    /**
    * Method to get the users zip code.
    * @return String zip
    */
    public String getZip() {
        return this.zip;
    }
    /**
     * Method to set the users zip code. This is optional on form.
     * @param z z Sets String zip
     */
    public void setZip(String z) {
        if(z==null){
            zip = "null";
        } else {
            this.zip = z;
        }
    }
    /**
    * Method to get the users telephone number.
    * @return String phone
    */
    public String getPhone() {
        return phone;
    }
    /**
     * Method to set the users telephone number.
     * @param phone phone Sets String phone
     */
    public void setPhone(String phone) {
        this.phone = phone;
    }
    /**
    * Method to get the users cell phone number.
    * @return String cell
    */
    public String getCell() {
        return cell;
    }
    /**
     * Method to set the users cell phone number.
     * @param cell cell Sets String cell
     */
    public void setCell(String cell) {
        this.cell = cell;
    }
    /**
    * Method to get the users e-mail address.
    * @return String email
    */
    public String getEmail() {
        return email;
    }
    /**
     * Method to set the users e-mail address.
     * @param e e Sets String email
     */
    public void setEmail(String e) {
        if(e.length()<1){
            this.email = null;
            this.errmsg = this.errmsg + "\n <li> Please enter an email address </li>";
        }else if(!e.contains("@")&&!e.contains(".")){
            this.email = null;
            this.errmsg = this.errmsg +"\n <li>Email is not valid.</li>";
        }else{
            this.email = e;
        }
        
    }
    /**
    * Method to get the users password.
    * @return String password
    */
    public String getPassword() {
        return password;
    }
    /**
     * Method to set the users password.
     * @param password password Sets String password
     */
    public void setPassword(String password) {
        if(password.length()<1){
            this.password = null;
            this.errmsg = this.errmsg +"\n <li> Please enter a valid password. </li>";
        }else{
            this.password = password;
        }
    }
    
    /**
     * Method to get users font color preference
     * @return String fontColor
     */
    public String getFontColor() {
        return fontColor;
    }
    /**
     * Method to set the users font color preference
     * @param fontColor Sets the font color preference
     */
    public void setFontColor(String fontColor) {
        this.fontColor = fontColor;
    }
    
    public String getStatus() {
        return status;
    }
    
    public void setStatus(String status) {
        this.status = status;
    }
    
    public String getValidation() {
        return validation;
    }
    
    public void setValidation(String validation) {
        this.validation = validation;
    }
    
    public Date getRegDate() {
        return regDate;
    }
    
    public void setRegDate(Date regDate) {
        this.regDate = regDate;
    }
    
    public Date getLastVisit() {
        return lastVisit;
    }
    
    public void setLastVisit(Date lastVisit) {
        this.lastVisit = lastVisit;
    }
    
    public String getDob(){
        return this.dob;
    }
    
    public void setDob(String d){
        this.dob = d;
    }
    
    public Set getClassification(){
        return this.classification;
    }
    
    public void setClassification(Set c){
        this.classification = c;
    }
    
    public Set getGroupList(){
        return this.groupMembershipSet;
    }
    
    public void addUser() {
        int theID = userList.size();
        theID = theID +1;
        userList.add(new User(theID, fname, lname, email, password));
        id = theID;
        
    }
    
    public void updateUser(int userID) {
        tempUser = (User)userList.get(userID+1);
        tempUser.fname = fname;
        tempUser.lname = lname;
        tempUser.password = password;
        tempUser.email = email;
    }
    
    public void login(String email, String password){
        validation = "no";
        for(int i = 0; i < userList.size(); i++){
            tempUser = (User)userList.get(i);
            if(tempUser.email == email){
                if(tempUser.password == password){
                    validation = "yes";
                    break;
                } else
                    validation = "no";
            }
        }
        if(validation.equals("yes")){
            id = tempUser.userID;
            email = tempUser.email;
            fname = tempUser.fname;
            lname = tempUser.lname;
            password = tempUser.password;
        }
    }
    
    private class User {
        public int userID;
        public String fname;
        public String lname;
        public String email;
        public String password;
        public User(int id, String f, String l, String e, String p){
            userID = id;
            fname = f;
            lname = l;
            email = e;
            password = p;
        }
    }
    
    public Event_DBImpl getEvent() {
        return event;
    }
    
    public void setEvent(Event_DBImpl event) {
        this.event = event;
    }
    
    public ResultSet getMyEvents(){
        event.setCreatorId(id);
        return event.getEventsByPersonId(id);
    }
    
    public ResultSet getMyPastEvents(){
        event.setCreatorId(id);
        return event.getPastEventsByPersonId(id);
    }
    public ResultSet getMyUpcomingEvents(){
        event.setCreatorId(id);
        return event.getUpcomingEventsByPersonId(id);
    }
    
    public void removeMyEvent(){
        
    }
    
    public ResultSet getAttendingEvents(){
        return event.getEventsByAttendant(id);
    }
    
    public ResultSet getAttendedEvents(){
        return event.getPastEventsByAttendant(id);
    }
    
    public ResultSet getAreaEvents(){
        return event.getEventsByState(state, id);
    }
    
    public boolean checkCalender(int eventId){
        boolean check = false;
        try{
            ResultSet rs = event.getEventByAttendant(eventId, id);
            if(rs.next())
                check = true;
            else
                check = false;
        } catch(SQLException se){
            //stub
        }
        return check;
    }
    
    public boolean checkDate(java.sql.Date date){
        java.util.GregorianCalendar gc = new GregorianCalendar();
        try{
          java.util.Date gcDate = event.convertGregorianDate(gc.getTime());
          int success = date.compareTo((java.sql.Date)gcDate);
          if(success < 0)
              return true;
        }
        catch(WrongFormatException wfe){
            //stub
        }
        
        return false;
    }
    
    public void addEventToCalendar(int eventId, int creator){
        conn = connectionPool.getConnection();
        
        //check date query
        String evtDateSQL = "select startDate from event where eventID = " + eventId + " AND" +
                " startDate < (select current_date())";
        
        //check if event is already added query
        String checkEvt = "select * from person_has_event pe where" +
                " pe.person_personID = " + id + " AND" +
                " pe.event_eventID = " + eventId + " AND" +
                " willAttend = 1";
        
        //add event query
        String addEvt = "insert into person_has_event (person_personID, event_eventID, " +
                "dateCreated, creator, willAttend) values " +
                "(?,?, (SELECT CURRENT_TIMESTAMP()),?,?)";
        
        try{
            java.sql.Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(checkEvt);
            if(rs.next()){
                errmsg = "Event already added to calendar.";
                welcomeMsg = "";
            } else{
                rs = stmt.executeQuery(evtDateSQL);
                //rs.next();
                if(rs.next()){
                    errmsg = "Event has passed";
                    welcomeMsg = "";
                }
                else{
                    java.sql.PreparedStatement ps = conn.prepareStatement(addEvt);
                    ps.setInt(1, id);
                    ps.setInt(2, eventId);
                    ps.setInt(3, creator);
                    ps.setInt(4, 1);
                    ps.executeUpdate();
                    setWelcomeMsg("Event Added Successfully");
                    setErrmsg("");
                }
            }
        } catch(SQLException se){
            errmsg = "SQL Error: " + se.getMessage();
        }
    }
    
    public String getWelcomeMsg() {
        return welcomeMsg;
    }
    
    public void setWelcomeMsg(String welcomeMsg) {
        this.welcomeMsg = welcomeMsg;
    }
    
    public String getErrmsg() {
        return errmsg;
    }
    
    public void setErrmsg(String errmsg) {
        this.errmsg = errmsg;
    }
    
    void removeEventFromCalendar(int eventId) throws SQLException {
        conn = connectionPool.getConnection();
        String removeSQL = "DELETE from person_has_event where person_personID = ? AND " +
                "event_eventID = ? AND " +
                "creator != ?";
        try {
            event.setEventById(eventId);
            if(event.getCreatorId() == id){
                errmsg = "You are the event creator.  Please go to your event management area if you must remove this event.";
                //event.removeEvent(eventId, id);
            } else{
                java.sql.PreparedStatement ps = conn.prepareStatement(removeSQL);
                ps.setInt(1, id);
                ps.setInt(2, eventId);
                ps.setInt(3, id);
                ps.executeUpdate();
                welcomeMsg = "Good Job.";
                connectionPool.freeConnection(conn);
            }
        } catch (WrongFormatException ex) {
            ex.printStackTrace();
            errmsg = "wrong format.";
        } catch (SQLException ex) {
            ex.printStackTrace();
            errmsg = "sql error." + ex.getMessage();
        }
    }
}
